package com.hnkywl.sts.dao.sys;

import com.hnkywl.sts.entity.sys.Js;
import com.hnkywl.sts.entity.sys.Yg;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Criteria;
import org.hibernate.criterion.*;
import org.hibernate.tool.hbm2x.StringUtils;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;


/**
 * 说明：
 * 包:        com.hnkywl.sts.dao.sys
 * 类描述:    岗位dao类
 * 修改备注: [说明本次修改内容]
 * 版本:        [v1.0]
 */
@Repository
public class YgDao extends SimpleHibernateTemplate<Yg, Long> {

    /**
     * 查询员工列表
     *
     * @param xm       员工名称
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Yg> findYgList(Long sjjgId, String xm, String gh, String dlm, Date rzsjStart, Date rzsjEnd, String cwbh, int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Yg> page = new Page<Yg>(pageNo, pageSize, 0);
        //==============条件拼接===============

        if (gh != null && !gh.isEmpty()) {
            condition.append(" gh like ? ");
            param.add("%" + gh.trim() + "%");
        }
        if (xm != null && !xm.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" xm like ? ");
            param.add("%" + xm.trim() + "%");
        }

        if (dlm != null && !dlm.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" dlm like ? ");
            param.add("%" + dlm.trim() + "%");
        }
        if (rzsjStart != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" rzsj >= ?");
            param.add(rzsjStart);
        }
        if (rzsjEnd != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" rzsj <= ?");
            param.add(rzsjEnd);
        }
        if (cwbh != null && !cwbh.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" cwbh = ? ");
            param.add(cwbh);
        }
        if (sjjgId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" zgjg.id = ? ");
            param.add(sjjgId);
        }
        //==============记录数量===============
        String dataRegion = "";
        if (YgService.getCurrentYgCxfw() == 2 || YgService.getCurrentYgCxfw() == 3) {
            dataRegion += " zgjg.id=" + YgService.getCurrentYgGsId();
        } else if (YgService.getCurrentYgCxfw() == 1) {
            dataRegion += " zggs.id=" + YgService.getCurrentYgZgGsId();
        } else {
            dataRegion += " 1=1";
        }
        hql = "select count(*) from Yg where " + dataRegion;
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Yg where " + dataRegion;
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    //供财务系统账户映射使用
    public Page<Yg> findAllYgByXmGs(String xm, Long gsId, int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Yg> page = new Page<Yg>(pageNo, pageSize, 0);
        //==============条件拼接===============

        if (xm != null && !xm.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" xm like ? ");
            param.add("%" + xm.trim() + "%");
        }
        if (gsId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" zggs.id = ? ");
            param.add(gsId);
        }
        //==============记录数量===============
        hql = "select count(*) from Yg where 1=1";
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Yg where 1=1";
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    //查询员工姓名列表
    public Page<Yg> findYgXmList(String ygxm, int pageNo, int pageSize) {
        Page<Yg> page = new Page<Yg>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("asc");
        page.setOrderBy("xm");
        if (StringUtils.isNotEmpty(ygxm)) {
            return this.findByCriteria(page, new Criterion[]{Restrictions.like("xm", ygxm, MatchMode.START)});
        } else
            return this.findAll(page);
    }

    /**
     * 查找主管机构下的员工
     *
     * @param zgjgId
     * @return
     */
    public List findYgListByZgjg(Long zgjgId) {
        return this.getSession().createCriteria(this.entityClass)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("xm").as("xm"))
                ).add(Restrictions.eq("zgjg.id", zgjgId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
                .list();
    }

    /**
     * 查找一级主管机构下的员工
     *
     * @param zgjgId
     * @return
     */
    public List<Yg> findAllYgListByZgjg(Long zgjgId) {
        return this.getSession().createCriteria(this.entityClass)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("xm").as("xm"))
                ).add(Restrictions.eq("yjzgjg.id", zgjgId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
                .list();
    }

    /**
     * 查找主管公司下的员工
     *
     * @param zggsId
     * @return
     */
    public List findYgListByZggsGw(Long zggsId, Long gwId) {
        Criteria criteria = this.getSession().createCriteria(this.entityClass)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("xm").as("xm"))
                ).add(Restrictions.eq("zgjg.id", zggsId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (gwId != null)
            criteria.add(Restrictions.eq("gw.id", gwId));
        return criteria.list();
    }

    /**
     * 查找主管公司下的员工
     *
     * @param zggsId
     * @return
     */
    public List findYgListByZggs(Long zggsId) {
        return this.getSession().createCriteria(this.entityClass)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("xm").as("xm"))
                ).add(Restrictions.eq("zggs.id", zggsId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
                .list();
    }


    /**
     * 重复性检查
     *
     * @param id
     * @param propertyName  属性名
     * @param propertyValue 属性值
     * @return
     */
    public Boolean checkUnique(Long id, String propertyName, Object propertyValue) {
        String hql;
        if (id == null) {
            //修改检验
            hql = "from Yg where " + propertyName + " = ? ";
            return this.existsQuery(hql, propertyValue);
        } else {
            //新增检验
            hql = "from Yg where " + propertyName + " = ? and id <>?";
            return this.existsQuery(hql, propertyValue, id);
        }
    }

    /**
     * 获得员工最大的排序号
     *
     * @return
     */
    public Integer getMaxPxh() {
        String hql = "select MAX(pxh) from Yg";
        return findInt(hql);
    }

    /**
     * 通过用户名和密码查找用户，用于登录
     *
     * @param userName 用户名
     * @param password 密码
     * @return 员工实体
     */
    public Map findYgByNameAndPass(String userName, String password) {
        Criteria critera = this.getSession().createCriteria(Yg.class)
                .createAlias("zgjg", "zgjg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("zggs", "zggs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("xm").as("xm"))
                        .add(Property.forName("cxfw").as("cxfw"))
                        .add(Property.forName("zggs.id").as("zggsid"))
                        .add(Property.forName("zggs.bm").as("zggsbm"))
                        .add(Property.forName("zggs.qc").as("zggsqc"))
                        .add(Property.forName("zggs.jc").as("zggsjc"))
                        .add(Property.forName("zggs.bm").as("zggsbm"))
                        .add(Property.forName("zgjg.id").as("jgid"))
                        .add(Property.forName("zgjg.qc").as("jgqc"))
                        .add(Property.forName("zgjg.jc").as("jgjc"))
                        .add(Property.forName("jqdlm").as("jqdlm"))
                        .add(Property.forName("jqdlmm").as("jqdlmm"))
                        .add(Property.forName("kdl").as("kdl"))
                ).add(Restrictions.eq("dlm", userName)).add(Restrictions.eq("dlmm", password))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (Map) critera.uniqueResult();
    }


    public List findYgAllJs(Long id) {
        Criteria critera = this.getSession().createCriteria(Js.class)
                .createAlias("ygList", "yg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gwList", "gw", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gw.ygList", "gw_yg", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                )
                .add(Restrictions.or(Restrictions.eq("yg.id", id), Restrictions.eq("gw_yg.id", id)))
                .setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
        return critera.list();

    }

    /**
     * 获取员工科目代码
     */
    public List<Map> findKmdm(Long gsId) {
        String sql = "select kmdm as \"kmdm\",xm as \"xm\" from t_sys_yg where deleted=0 and length(rtrim(ltrim(kmdm)))!=0 and zggs_id=?";
        List<Map> result = createSQLQuery(sql, gsId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }

    /**
     * 员工导入电话号码根据excel表格条件查询员工
     *
     * @param xm
     * @param zggs
     * @param zgjg
     * @return
     */
    public Yg findYgByXm(String xm, String zggs, String zgjg) {
        String hql = "from Yg where xm like ? and zggs.jc like ? and zgjg.jc like ? ";
        return (Yg) createQuery(hql, "%" + xm.trim() + "%", "%" + zggs.trim() + "%", "%" + zgjg.trim() + "%").list().get(0);
    }

}
